package jwr.ex6;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.spi.DirStateFactory.Result;


public class JDBCpractice implements JDBCPracticeService{
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	private String col="";
	
	private final String insertSQL="insert into bookdb values(?,?,?,?)";
	private final String selectSQL="select * from bookdb";
//	private final String updateSQL="update bookdb set " + getCol()+ " = ? where "+ getCol()+" = ?";
	private final String deleteSQL="delete from bookdb where id = ?";
	public String getCol() {
		return col;
	}

	public void setCol(String col) {
		this.col = col;
	}
	
	Connection conn = null;
	PreparedStatement ps = null;
	
	public JDBCpractice() {
		System.out.println(selectJDBC());
	}
	
	@Override
	public boolean insertJDBC(int id,String bookTitle, String publisher, String AUTHOR) {
		boolean result =false;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			ps.setInt(1, id); 
			ps.setString(2, bookTitle); 
			ps.setString(3, publisher); 
			ps.setString(4, AUTHOR); 
			int cnt = ps.executeUpdate();
			if(cnt>0){
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps != null) try{ps.close();}catch(SQLException e) {e.printStackTrace();}
			if(conn!=null) try{conn.close();} catch(SQLException e2){e2.printStackTrace();}
		}
		return result;
	}
	@Override
	public String selectJDBC() {
		String result="";
		ResultSet rs = null;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			while(rs.next()){
				result+=rs.getInt(1)+" ";
				result+=rs.getString(2)+" ";
				result+=rs.getString(3)+" ";
				result+=rs.getString(4)+"\n";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null) try{rs.close();} catch(SQLException e){e.printStackTrace();}
			if(ps!=null) try{ps.close();} catch(SQLException e2){e2.printStackTrace();}
			if(conn!=null) try{conn.close();} catch(SQLException e3){e3.printStackTrace();}
		}
		return result;
	}
	@Override
	public boolean updateJDBC(String col,String nowData,String newData) {
		setCol(col);
		String updateSQL="update bookdb set " + getCol()+ " = ? where "+ getCol()+" = ?";
		boolean result=false;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, newData);
			ps.setString(2, nowData);
			int cnt = ps.executeUpdate();
			if(cnt>0){
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null) try {ps.close();} catch (Exception e2) {	}
			if(conn!=null) try {
				conn.close();
			} catch (Exception e2) {
			}
		}
		
		return result;
	}
	@Override
	public boolean deleteJDBC(int id) {
		boolean result = false;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
			ps.setInt(1, id);
			int cnt = ps.executeUpdate();
			if(cnt > 0){
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(ps!=null) try {
				ps.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			if(conn!=null) try {
				conn.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return result;
	}
	
	
}
